<?php
namespace app\common\controller;

use think\Controller;
use think\Request;
use think\Session;
use think\Response;
use constant\OrderStatus;
use constant\PayStatus;
use constant\ShippingStatus;
use app\common\model\Deadbeat as DeadbeatModel;

/**
 * Excel导入导出
 */
class Excel extends Controller {



/**************************************导入*********************************************/
	// 导入字段
	private $importFields = array(
		// 老赖分拆费用
		'deadbeat_cost' => array(
			'0'  => 'name', // 姓名
			'1'  => 'card_sn', // 卡号
			'2'  => 'principal',//本金
			'3'  => 'interest',//利息
			'4'  => 'staging_fee_balance',//分期手续费
			'5'  => 'late_fee',//滞纳金
			'6'  => 'other2',//违约金
			'7'  => 'overlimit_fee',//超限费
			'8'  => 'loss_fee',//挂失费
			'9'  => 'annual_fee',//年费
			'10'  => 'card_fee',//补卡费
			'11'  => 'poundage_fee',//取现手续费
			'12'  => 'increment1',//用卡无忧
			'13'  => 'increment2',//信用保障
			'14'  => 'transfer_accounts_fee',//转账手续费
			'15'  => 'query_fee',//银联境外账户查询手续费
			'16'  => 'total_fee',//总金额
			'17'  => 'date_of_entry',//账单截至日
		),

		// 老赖主信息
		'deadbeat_main' => array(
			'0'  => 'name', // 姓名
			'1'  => 'identity_sn',//身份证号
			'2'  => 'sex',//性别
			'3'  => 'birthday',//出生年月日
			'4'  => 'nation',//民族
			'5'  => 'permanent_address',//户籍地址
			'6'  => 'card_sn', // 卡号
			'7'  => 'date_of_open',//开户日期
			'8'  => 'address',//地址
			'9'  => 'phone',//手机
			'10'  => 'telephone',//固话
			'11'  => 'last_date',//文档最后中文日期
		),

	);


	/**
	 * @desc 导入
	 */
	public function import($act) {
		set_time_limit(0);

		if (!$act){
		 	$this->error('非法操作');
		}

		$resource               = controller('file/FileManager');
        $excelData              = $resource->upload();
        $excelData              = json_decode($excelData);
        $extension              = $excelData->data->$act->ext;
        $relative_path          = $excelData->data->$act->relative_path;

		$excelArray = $this->_excelToArray($relative_path, $extension, $act);

		switch ($act) {
			// 分拆费用
			case 'deadbeat_cost':
				$return = $this->_importCost($excelArray);
				break;
			case 'deadbeat_main':
				$return = $this->_importMain($excelArray);
				break;
			default:
				$this->error('非法操作');
		}

	}


	/**
	 * @desc Excel文件内容转换成数组
	 * @param string $file_path: 文件路径
	 * @param string $file_ext : 文件后缀
	 * @return multitype
	 */
	private function _excelToArray($file_path, $file_ext, $mode = '') {
		switch ($file_ext) {
			case 'xls':
			$type = 'Excel5';
			break;
			case 'xlsx':
			$type = 'Excel2007';
			break;
			default:
			$type = 'Excel5';
			break;
		}

		$objReader   = \PHPExcel_IOFactory::createReader($type);
		$objReader->setReadDataOnly(true);
		$objPHPExcel = $objReader->load($file_path);
		$objSheet    = $objPHPExcel->getActiveSheet();

		$highestRow         = $objSheet->getHighestRow();    // 最后一行行号（数字）
		$highestColumn      = $objSheet->getHighestColumn(); // 最后一列列号（英文）

		$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); // 总列数

			// var_dump($highestRow );
			// var_dump($highestColumnIndex );
			// var_dump($highestColumn );die;

		$excelData = array();
		for ($row = 1; $row <= $highestRow; $row++) {
			for ($col = 0; $col < $highestColumnIndex; $col++) {
				if ($mode) {
					$val = $objSheet->getCellByColumnAndRow($col, $row)->getValue();
					if (!empty($val) or $val == 0 ) {

						// $excelData[$row][$this->importFields[$mode][$col]] = trim( (string) $objSheet->getCellByColumnAndRow($col, $row)->getValue() ); // 获取excel单元格显示值
						$excelData[$row][$this->importFields[$mode][$col]] = trim( (string) $objSheet->getCellByColumnAndRow($col, $row)->getCalculatedValue() ); // 获取excel单元格公式值
					}
				} 
			}
		}

		// 删除Excel文件
		unlink($file_path);

		return $excelData;
	}



	/**
	 * @param multitype 
	 */
	private function _importCost($excelArray) {
// var_dump($excelArray);die;

		$require_field = array('name','card_sn'); // 必填字段

		//批量添加数组
		$DeviceSnList = array(); 
		$i       = 0;
		$now = time();
		$dcModel = Db('Deadbeat');

		foreach ($excelArray as $row => $data) {
			if ($row == 1){
				continue; // 跳过第一行（标题行）
			}

			// 验证必填项
			$flag = true;
			foreach ($require_field as $field) {
				if (!$data[$field]){
					$flag = false;
				}
			}
			if (!$flag){
				continue;
			}

			// 验证卡号是否已存在
			$has = $dcModel->where('card_sn',$data['card_sn'])->value('id');
			if ($has){
				continue;
			}

			$DeviceSnList[$i] = array(
						'name'                  => $data['name'],// 名字
						'card_sn'               => $data['card_sn'], 
						'principal'             => (float)$data['principal'], 
						'interest'              => (float)$data['interest'], 
						'staging_fee_balance'   => (float)$data['staging_fee_balance'], 
						'late_fee'              => (float)$data['late_fee'], 
						'other2'                => (float)$data['other2'], 
						'overlimit_fee'         => (float)$data['overlimit_fee'], 
						'loss_fee'              => (float)$data['loss_fee'], 
						'annual_fee'            => (float)$data['annual_fee'], 
						'card_fee'              => (float)$data['card_fee'], 
						'poundage_fee'          => (float)$data['poundage_fee'], 
						'increment1'            => (float)$data['increment1'], 
						'increment2'            => (float)$data['increment2'], 
						'transfer_accounts_fee' => (float)$data['transfer_accounts_fee'], 
						'query_fee'             => (float)$data['query_fee'], 
						'total_fee'             => (float)$data['total_fee'],
						'date_of_entry'         => $data['date_of_entry']
                    );

			$i = ++$i;
		}

		$res = Db('Deadbeat')->insertAll($DeviceSnList);

		if ($res) {
			$this->success('成功导入' . $res . '条数据');
		} else {
			$this->error('没有导入任何数据');
		}
	}


	/**
	 * @param multitype 
	 */
	private function _importMain($excelArray) {
// var_dump($excelArray);die;
		$require_field = array('card_sn'); // 必填字段

		$i       = 0;

		foreach ($excelArray as $row => $data) {
			if ($row == 1){
				continue; // 跳过第一行（标题行）
			}

			// 验证必填项
			$flag = true;
			foreach ($require_field as $field) {
				if (!$data[$field]){
					$flag = false;
				}
			}
			if (!$flag){
				continue;
			}

			// 验证卡号是否已存在
			$deadbeatModel = DeadbeatModel::get(['card_sn' => $data['card_sn']]);
			if ($deadbeatModel){
				$deadbeatModel->name              = $data['name'];
				$deadbeatModel->identity_sn       = $data['identity_sn'];
				$deadbeatModel->sex               = $data['sex'];
				$deadbeatModel->birthday          = $data['birthday'];
				$deadbeatModel->nation            = $data['nation'];
				$deadbeatModel->permanent_address = $data['permanent_address'];
				$deadbeatModel->address           = $data['address'];
				$deadbeatModel->date_of_open      = $data['date_of_open'];
				$deadbeatModel->phone             = $data['phone'];
				$deadbeatModel->telephone         = $data['telephone'];
				$deadbeatModel->last_date         = $data['last_date'];
				$deadbeatModel->save();
				$i = ++$i;
			}
		
		}

		if ($i > 0) {
			$this->success('成功导入'. $i.'条数据');
		} else {
			$this->error('没有导入任何数据');
		}
	}



	/**************************************导出*********************************************/

		/**
		 * @desc 导出
		 */
		public function export($act) {
			set_time_limit(0);

			$objPHPExcel = new \PHPExcel();

			if (!$act) {
				$this->error('非法操作');
			}

			switch ($act) {

				//订单列表
				case 'order_list':
					$this->_exportOrderList($objPHPExcel);
					break;

				default:
					break;
			}
		}


		/**
		 * @desc 下载
		 * @param object $objPHPExcel
		 * @param string $filename
		 */
		private function download($objPHPExcel, $filename = 'export.xls') {
			//存到临时文件夹excel
			header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');  
			header('Content-Disposition: attachment;filename="'.$filename.'.xls"');  
			header('Cache-Control: max-age=0');  
			$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
			// 其他写法
			// $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
			// $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

			return $this->saveExcelToLocalFile($objWriter,$filename);
		}

		/**
		 * @author 李嘉华
		 * @desc 返回excel下载地址
		 */
		private function saveExcelToLocalFile($objWriter,$filename){
		    // make sure you have permission to write to directory
		    $filePath = config('DOWNLOADS_EXCEL') . DS . $filename . '.xls';
		    $objWriter->save($filePath);

		    return $filePath;
		}





		/**
		 * @desc 订单列表
		 */
		private function _exportOrderList($objPHPExcel) {
			$data = $this->request->param('');
			$_where = $data['where'];

			$where       = my_unserialize($_where);

			$info = Db('Order')->where($where)->select()->toArray();
			$OrderGoodsViewModel = model('OrderGoodsView');

			foreach ($info as $k => $v) {
				$orderGoodsInfo = array();
				$orderGoodsInfo = $OrderGoodsViewModel->where('platform_order_goods.order_id',$v['id'])->select()->toArray();
				$str = '';
				foreach ($orderGoodsInfo as $k1 => $v1) {
					$str .= '商品货号：'.$v1['goods_sn'] .' 商品名称：'.$v1['goods_name'].' 商品规格：'.$v1['spec_key_name']. ' 数量：'. $v1['quantity'] .'||';
				}
				$info[$k]['goodsInfo'] = $str;
			}

			$thead = array('订单号', '创建时间', '收货人' ,'收货地址', '联系电话' , '实付金额', '支付方式', '支付状态', '发货状态', '商品信息');
			$cols = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');

			$line = 1;
			if ($info) {
				$objPHPExcel->getProperties()->setTitle('订单列表');
				$objPHPExcel->setActiveSheetIndex(0);

				foreach ($thead as $k => $th) {
					$objPHPExcel->getActiveSheet()->setCellValue($cols[$k] . $line, $th);
				}

				foreach ($cols as $k => $v) {
					//设置每行的宽度自适应
					$objPHPExcel->getActiveSheet()->getColumnDimension($v)->setAutoSize(true);
				}

				foreach ($info as $key => $data) {
					$line++;
					$objPHPExcel->getActiveSheet()->setCellValue($cols[0] . $line, $data['order_sn']);

					$add_time = date("Y-m-d H:i:s",$data['add_time']);
					$objPHPExcel->getActiveSheet()->setCellValue($cols[1] . $line, $add_time);

					$objPHPExcel->getActiveSheet()->setCellValue($cols[2] . $line, $data['consignee']);

					$address = $data['province'].' '.$data['city'].' '.$data['area'].' '.$data['addr'];
					$objPHPExcel->getActiveSheet()->setCellValue($cols[3] . $line, $address);

					$objPHPExcel->getActiveSheet()->setCellValue($cols[4] . $line, $data['mobile']);

					$objPHPExcel->getActiveSheet()->setCellValue($cols[5] . $line, $data['actual_price']);

					$objPHPExcel->getActiveSheet()->setCellValue($cols[6] . $line, $data['pay_name']);

					$pay_status = PayStatus::$cn[$data['pay_status']];
					$objPHPExcel->getActiveSheet()->setCellValue($cols[7] . $line, $pay_status);

					$shipping_status = ShippingStatus::$cn[$data['shipping_status']];
					$objPHPExcel->getActiveSheet()->setCellValue($cols[8] . $line, $shipping_status);

					$objPHPExcel->getActiveSheet()->setCellValue($cols[9] . $line, $data['goodsInfo']);
				}
			}

			$filename = 'order_list_' . time() . '_' . GetRandStr(4);

			$data = $this->download($objPHPExcel, $filename);

			$this->success($data);
		}






}
